﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;
using System.Data;

namespace Kiosk
{
    /// <summary>
    /// Interaction logic for BrowseMentors.xaml
    /// </summary>
    public partial class BrowseMentors : Window
    {
        private bool boolStopFilterEvents;
        private List<string> listDegree;
        private List<string> listExperience;
        private List<string> listCourses;
        private List<string> listLocation;
        private List<string> listJobType;
        private List<KeyValuePair<string, string>> listDegreeKVP;
        private List<KeyValuePair<string, string>> listExperienceKVP;
        private List<KeyValuePair<string, string>> listCoursesKVP;
        private List<KeyValuePair<string, string>> listLocationKVP;
        private List<KeyValuePair<string, string>> listJobTypeKVP;

        List<Mentor> listMentors = new List<Mentor>();
        private DataSet dsFilterMentors = new DataSet();
        private DataSet dsMentors = new DataSet();
        private int returnCount = 0;
        public Window previousPage = new Window();
        private bool isFilterChangedValid = true;

        public BrowseMentors()
        {
            InitializeComponent();
            boolStopFilterEvents = false;
            createComboBoxes();
        }

        private void updateMentorsView(ComboBox changedCB)
        {
            listMentors = new List<Mentor>();
            dsMentors = new DataSet();
            string whereParams = "";
            if (sGlobals.selectedPathwayID.Count > 0)
            {
                whereParams = "WHERE (mentor.pathway_id = " + string.Join(" OR mentor.pathway_id = ", sGlobals.selectedPathwayID) + ") AND mentor.deletion_date IS NULL";
            }

            string sql = "SELECT mentor_id, CONCAT_WS(' ',first_name, last_name) AS fullname, degree_name, pathway_name, mentor_id, photo FROM mentor " +
                           "LEFT JOIN lkup_pathway ON mentor.pathway_id = lkup_pathway.pathway_id " +
                           "LEFT JOIN lkup_degree ON mentor.degree = lkup_degree.degree_id " +
                            whereParams + buildMentorQuery();
            try
            {
                dsMentors = sGlobals.dbWrapper.ExecuteSQLStatement(sql, ref returnCount);
                if (returnCount > 0)
                {
                    lblTitle.Content = "ADVANCED FILTER MENTORS";
                    lblcount.Content = returnCount.ToString() + " RESULT(S)";
                    for (int i = 0; i < dsMentors.Tables[0].Rows.Count; i++)
                    {
                        if (((byte[])dsMentors.Tables[0].Rows[i]["photo"]).Count() == 0) // use placeholder image
                        {

                            listMentors.Add(new Mentor(
                                dsMentors.Tables[0].Rows[i]["fullname"].ToString(),
                                dsMentors.Tables[0].Rows[i]["pathway_name"].ToString(),
                                dsMentors.Tables[0].Rows[i]["degree_name"].ToString(),
                                dsMentors.Tables[0].Rows[i]["mentor_id"].ToString()));
                        }
                        else //use photo
                        {
                            listMentors.Add(new Mentor(
                                dsMentors.Tables[0].Rows[i]["fullname"].ToString(),
                                dsMentors.Tables[0].Rows[i]["pathway_name"].ToString(),
                                dsMentors.Tables[0].Rows[i]["degree_name"].ToString(),
                                (byte[])dsMentors.Tables[0].Rows[i]["photo"],
                                dsMentors.Tables[0].Rows[i]["mentor_id"].ToString()));
                        }

                    }
                    lbAdv.ItemsSource = listMentors;
                }
                else
                {
                    WarningMessage warn = new WarningMessage();
                    warn.lblMessage.Content = "There are no mentors to display with the selected filters";
                    warn.ShowDialog();
                    if (changedCB != null)
                        changedCB.SelectedIndex = 0;
                }
            }
            catch (Exception ex)
            {
                if (!sGlobals.hasLogout)
                {
                    WarningMessage warning = new WarningMessage();
                    warning.lblMessage.Content = "There was a problem loading the mentors.\r\nIf the problem persists, please contact your system administrator";
                    warning.ShowDialog();
                }
                lblcount.Content = "0 RESULT(S)";
            }
        }

        private void _listBoxItem_MouseLeftButtonDown(object sender, MouseButtonEventArgs e)
        {
            AdvisorBox selectedBox = (AdvisorBox)sender;
            sGlobals.selectedMentorID = selectedBox.mentorID.Content.ToString();
            string viewMentorLogQuery = "INSERT INTO lgd_mentor_student_actions (mentor_id, student_id, date_time, " +
                    "action_performed_id) VALUES ('" + sGlobals.selectedMentorID + "', (SELECT user_id FROM users WHERE username = '" +
                    sGlobals.currentUsername + "'), NOW(), 'V')";
            try
            {
                sGlobals.dbWrapper.ExecuteNonQuery(viewMentorLogQuery);
            }
            catch (Exception ex)
            {
                Console.WriteLine("There was a problem connecting to the database with exception: {0}", ex.Message);
            }
            sGlobals.mentorDisplay.previousPage = this;
            sGlobals.mentorDisplay.Show();
            sGlobals.browseMentors.Hide();
        }

        private void btnBack_Click(object sender, RoutedEventArgs e) //back
        {
            listMentors.Clear();
            dsMentors.Clear();
            sGlobals.pathways.Show();
            sGlobals.browseMentors.Hide();
        }

        private void btnLogout_Click(object sender, RoutedEventArgs e) //logout
        {
            sGlobals.logout();
        }

        private void Window_IsVisibleChanged(object sender, DependencyPropertyChangedEventArgs e)
        {
            Window currWindow = (Window)sender;
            if (currWindow.IsVisible && previousPage == sGlobals.pathways)
            {
                isFilterChangedValid = false;
                Filter_Gender.SelectedIndex = 0;
                Filter_Education.SelectedIndex = 0;
                Filter_Experience.SelectedIndex = 0;
                Filter_Job_Type.SelectedIndex = 0;
                Filter_Location.SelectedIndex = 0;
                Filter_Courses_1.SelectedIndex = 0;
                isFilterChangedValid = true;
                this.updateMentorsView(null);
            }
        }

        private void FilterChanged(object sender, SelectionChangedEventArgs e)
        {
            if (boolStopFilterEvents)
            {
                return;
            }
            else
            {
                ComboBox tempCB = (ComboBox)sender;
                if (isFilterChangedValid)
                {
                    updateMentorsView(tempCB);
                }
            }
        }

        private string buildMentorQuery()
        {
            string query = "";
            if (Filter_Gender.SelectedIndex != 0)
            {
                switch (((ComboBoxItem)Filter_Gender.SelectedItem).Content.ToString())
                {
                    case "Male":
                        query += " AND mentor.gender = 'M'";
                        break;
                    case "Female":
                        query += " AND mentor.gender = 'F'";
                        break;
                    default:
                        break;
                }
            }
            if (Filter_Education.SelectedIndex != 0 && Filter_Education.SelectedItem != null)
            {
                query += " AND mentor.degree = (SELECT degree_id FROM lkup_degree WHERE degree_name = '" + Filter_Education.SelectedValue + "')";
            }
            if (Filter_Job_Type.SelectedIndex != 0)
            {
                query += " AND mentor.job_type = (SELECT job_type_id FROM lkup_job_type WHERE job_type_text = '" + Filter_Job_Type.SelectedValue + "')";
            }
            if (Filter_Experience.SelectedIndex != 0)
            {
                query += " AND mentor.years_experience_group = (SELECT range_id FROM lkup_year_range WHERE range_text = '" + Filter_Experience.SelectedValue + "')";
            }
            if (Filter_Location.SelectedIndex != 0)
            {
                query += " AND mentor.location = (SELECT location_id FROM lkup_location WHERE location_text = '" + Filter_Location.SelectedValue + "')";
            }
            if (Filter_Courses_1.SelectedIndex != 0)
            {
                query += " AND (mentor.benneficial_course1 = (SELECT course_id FROM lkup_courses WHERE course_name = '" + Filter_Courses_1.SelectedValue + "')" + "OR mentor.benneficial_course2 = (SELECT course_id FROM lkup_courses WHERE course_name = '" + Filter_Courses_1.SelectedValue + "')" + "OR mentor.benneficial_course3 = (SELECT course_id FROM lkup_courses WHERE course_name = '" + Filter_Courses_1.SelectedValue + "'))";
            }
            return query;
        }

        private void createComboBoxes()
        {
            #region List Initialization
            listDegree = new List<string>();
            listExperience = new List<string>();
            listCourses = new List<string>();
            listLocation = new List<string>();
            listJobType = new List<string>();
            listDegreeKVP = new List<KeyValuePair<string, string>>();
            listExperienceKVP = new List<KeyValuePair<string, string>>();
            listCoursesKVP = new List<KeyValuePair<string, string>>();
            listLocationKVP = new List<KeyValuePair<string, string>>();
            listJobTypeKVP = new List<KeyValuePair<string, string>>();
            dsFilterMentors = new DataSet();

            listDegree.Add(" ");
            listDegreeKVP.Add(new KeyValuePair<string, string>(" ", " "));
            listExperience.Add(" ");
            listExperienceKVP.Add(new KeyValuePair<string, string>(" ", " "));
            listCourses.Add(" ");
            listCoursesKVP.Add(new KeyValuePair<string, string>(" ", " "));
            listLocation.Add(" ");
            listLocationKVP.Add(new KeyValuePair<string, string>(" ", " "));
            listJobType.Add(" ");
            listJobTypeKVP.Add(new KeyValuePair<string, string>(" ", " "));
            #endregion List Initialization

            string sql = "SELECT degree_name, degree_id FROM lkup_degree";
            try
            {
                dsFilterMentors = sGlobals.dbWrapper.ExecuteSQLStatement(sql, ref returnCount);
                if (returnCount > 0)
                {
                    for (int i = 0; i < dsFilterMentors.Tables[0].Rows.Count; i++)
                    {
                        listDegree.Add(dsFilterMentors.Tables[0].Rows[i]["degree_name"].ToString());
                        listDegreeKVP.Add(new KeyValuePair<string, string>(dsFilterMentors.Tables[0].Rows[i]["degree_name"].ToString(), dsFilterMentors.Tables[0].Rows[i]["degree_id"].ToString()));
                    }
                    Filter_Education.ItemsSource = listDegree;
                }

                dsFilterMentors = new DataSet();
                sql = "SELECT job_type_text, job_type_id FROM lkup_job_type";
                dsFilterMentors = sGlobals.dbWrapper.ExecuteSQLStatement(sql, ref returnCount);
                if (returnCount > 0)
                {
                    for (int i = 0; i < dsFilterMentors.Tables[0].Rows.Count; i++)
                    {
                        listJobType.Add(dsFilterMentors.Tables[0].Rows[i]["job_type_text"].ToString());
                        listJobTypeKVP.Add(new KeyValuePair<string, string>(dsFilterMentors.Tables[0].Rows[i]["job_type_text"].ToString(), dsFilterMentors.Tables[0].Rows[i]["job_type_id"].ToString()));
                    }
                    Filter_Job_Type.ItemsSource = listJobType;
                }

                dsFilterMentors = new DataSet();
                sql = "SELECT location_text, location_id FROM lkup_location";
                dsFilterMentors = sGlobals.dbWrapper.ExecuteSQLStatement(sql, ref returnCount);
                if (returnCount > 0)
                {
                    for (int i = 0; i < dsFilterMentors.Tables[0].Rows.Count; i++)
                    {
                        listLocation.Add(dsFilterMentors.Tables[0].Rows[i]["location_text"].ToString());
                        listLocationKVP.Add(new KeyValuePair<string, string>(dsFilterMentors.Tables[0].Rows[i]["location_text"].ToString(), dsFilterMentors.Tables[0].Rows[i]["location_id"].ToString()));
                    }
                    Filter_Location.ItemsSource = listLocation;
                }

                dsFilterMentors = new DataSet();
                sql = "SELECT range_text, range_id FROM lkup_year_range";
                dsFilterMentors = sGlobals.dbWrapper.ExecuteSQLStatement(sql, ref returnCount);
                if (returnCount > 0)
                {
                    for (int i = 0; i < dsFilterMentors.Tables[0].Rows.Count; i++)
                    {
                        listExperience.Add(dsFilterMentors.Tables[0].Rows[i]["range_text"].ToString());
                        listExperienceKVP.Add(new KeyValuePair<string, string>(dsFilterMentors.Tables[0].Rows[i]["range_text"].ToString(), dsFilterMentors.Tables[0].Rows[i]["range_id"].ToString()));
                    }
                    Filter_Experience.ItemsSource = listExperience;
                }

                dsFilterMentors = new DataSet();
                sql = "SELECT course_name, course_id FROM lkup_courses";
                dsFilterMentors = sGlobals.dbWrapper.ExecuteSQLStatement(sql, ref returnCount);
                if (returnCount > 0)
                {
                    for (int i = 0; i < dsFilterMentors.Tables[0].Rows.Count; i++)
                    {
                        listCourses.Add(dsFilterMentors.Tables[0].Rows[i]["course_name"].ToString());
                        listCoursesKVP.Add(new KeyValuePair<string, string>(dsFilterMentors.Tables[0].Rows[i]["course_name"].ToString(), dsFilterMentors.Tables[0].Rows[i]["course_id"].ToString()));
                    }
                    Filter_Courses_1.ItemsSource = listCourses;
                }
            }
            catch (Exception ex)
            {
                if (!sGlobals.hasLogout)
                {
                    WarningMessage warning = new WarningMessage();
                    warning.lblMessage.Content = "There was a problem loading the page.\r\nIf the problem persists, please contact your system administrator";
                    warning.ShowDialog();
                }
            }
        }

        private void btnClearFilters_Click(object sender, RoutedEventArgs e)
        {
            //changing the index on all the box would make 6 different query calls and image decryption
            //this makes it only happen once
            this.boolStopFilterEvents = true;
            Filter_Courses_1.SelectedIndex = 0;
            Filter_Education.SelectedIndex = 0;
            Filter_Experience.SelectedIndex = 0;
            Filter_Gender.SelectedIndex = 0;
            Filter_Job_Type.SelectedIndex = 0;            
            Filter_Location.SelectedIndex = 0;
            this.boolStopFilterEvents = false;
            this.updateMentorsView(null);
        }
    }
}
